/*
 * Copyright (C) 2011 TopCoder Inc., All Rights Reserved.
 */
package gov.nasa.pds.services.impl;

import gov.nasa.pds.entities.DataSet;
import gov.nasa.pds.entities.DataSetSearchCriteria;
import gov.nasa.pds.entities.HelpFile;
import gov.nasa.pds.entities.Instrument;
import gov.nasa.pds.entities.MetadataObject;
import gov.nasa.pds.entities.Mission;
import gov.nasa.pds.entities.PagedResults;
import gov.nasa.pds.entities.Reference;
import gov.nasa.pds.entities.Target;
import gov.nasa.pds.entities.TargetType;
import gov.nasa.pds.entities.Volume;
import gov.nasa.pds.services.AdHocQueryTranslator;
import gov.nasa.pds.services.DataSetProcessingException;
import gov.nasa.pds.services.DataSetService;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import org.springframework.beans.factory.InitializingBean;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;

import com.topcoder.util.log.Log;

/**
 * <p>
 * A comprehensive service implementation to get data for the users.
 * </p>
 *
 * <p>
 * <b>Thread Safety:</b> The implementations are effectively thread-safe.
 * </p>
 *
 * @author argolite, TCSASSEMBLER
 * @version 1.0
 */
public class JDBCDataSetService implements DataSetService, InitializingBean {
    /**
     * Represents the class name.
     */
    private static final String CLASS_NAME = JDBCDataSetService.class.getName();

    /**
     * Represents the JdbcTemplate instance used for all DB interaction.
     */
    private JdbcTemplate jdbcTemplate;

    /**
     * Represents the log instance used for logging.
     */
    private Log logger;

    /**
     * Represents the numeric threshold below which a rating is deemed to be insufficient, hence a white
     * spot.
     */
    private float whiteSpotRatingThreshold;

    /**
     * Represents the percent threshold below which a rating is deemed to be insufficient, hence a white
     * spot. This rating will be measured respective to the 'best' rating in the system.
     */
    private float whiteSpotRatingPercentThreshold;

    /**
     * Represents the AdHocQueryTranslator used to translate the adHoc query into something more structured
     * to be used by this service, which in the impl's case will be SQL query ready to be plugged into the
     * jdbcTemplate.
     */
    private AdHocQueryTranslator adHocQueryTranslator;

    /**
     * Represents the number of popular queries to return when requested.
     */
    private int popularQueryRetrievalThreshold;

    /**
     * The default do nothing constructor.
     */
    public JDBCDataSetService() {
    }

    /**
     * Sets the JdbcTemplate instance used for all DB interaction.
     * @param jdbcTemplate the JdbcTemplate instance used for all DB interaction
     */
    public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {
        this.jdbcTemplate = jdbcTemplate;
    }

    /**
     * Sets the log instance used for logging.
     * @param logger the log instance used for logging
     */
    public void setLogger(Log logger) {
        this.logger = logger;
    }

    /**
     * Sets the numeric threshold below which a rating is deemed to be insufficient, hence a white spot.
     * @param whiteSpotRatingThreshold the numeric threshold below which a rating is deemed to be
     *                                 insufficient, hence a white spot
     */
    public void setWhiteSpotRatingThreshold(float whiteSpotRatingThreshold) {
        this.whiteSpotRatingThreshold = whiteSpotRatingThreshold;
    }

    /**
     * Sets the percent threshold below which a rating is deemed to be insufficient.
     * @param whiteSpotRatingPercentThreshold the percent threshold below which a rating is deemed to be insufficient
     */
    public void setWhiteSpotRatingPercentThreshold(float whiteSpotRatingPercentThreshold) {
        this.whiteSpotRatingPercentThreshold = whiteSpotRatingPercentThreshold;
    }

    /**
     * Sets the AdHocQueryTranslator used to translate the adHoc query into something more structured
     * to be used by this service.
     * @param adHocQueryTranslator the AdHocQueryTranslator used to translate the adHoc query into
     *                             something more structured to be used by this service
     */
    public void setAdHocQueryTranslator(AdHocQueryTranslator adHocQueryTranslator) {
        this.adHocQueryTranslator = adHocQueryTranslator;
    }

    /**
     * Sets the number of popular queries to return when requested.
     * @param popularQueryRetrievalThreshold the number of popular queries to return when requested
     */
    public void setPopularQueryRetrievalThreshold(int popularQueryRetrievalThreshold) {
        this.popularQueryRetrievalThreshold = popularQueryRetrievalThreshold;
    }

    /**
     * Checks whether this class was initialized by Spring properly.
     *
     * @throws DataSetProcessingException if logger or jdbcTemplate or whiteSpotRatingThreshold
     *                                    or whiteSpotRatingPercentThreshold or adHocQueryTranslator
     *                                    or popularQueryRetrievalThreshold are null
     */
    @Override
    public void afterPropertiesSet() throws DataSetProcessingException {
        Helper.checkNotNull("jdbcTemplate", jdbcTemplate);
        Helper.checkNotNull("logger", logger);
        Helper.checkNotNull("whiteSpotRatingThreshold", whiteSpotRatingThreshold);
        Helper.checkNotNull("whiteSpotRatingPercentThreshold", whiteSpotRatingPercentThreshold);
        Helper.checkNotNull("adHocQueryTranslator", adHocQueryTranslator);
        Helper.checkNotNull("popularQueryRetrievalThreshold", popularQueryRetrievalThreshold);
    }

    /**
     * Gets the help file with the given name.
     *
     * @param name the name of the help file
     * @return the HelpFile that provides the path to the help file
     * @throws DataSetProcessingException if there is an error while getting the data
     */
    public HelpFile getHelpFile(String name) throws DataSetProcessingException {
        String methodName = CLASS_NAME + ".getHelpFile(String name)";
        Helper.logMethodEntryAndInputParameters(logger, methodName, new String[] {"name"},
                new Object[] {name});

        try {
            List<HelpFile> helpFiles = jdbcTemplate.query(
                    "select id, name, path from help_file where name = ?",
                    new Object[] {name},
                    new RowMapper<HelpFile>() {
                        public HelpFile mapRow(ResultSet rs, int rowNum) throws SQLException {
                            HelpFile helpFile = new HelpFile();
                            helpFile.setId(rs.getLong("id"));
                            helpFile.setName(rs.getString("name"));
                            helpFile.setPath(rs.getString("path"));
                            return helpFile;
                        }
                    });
            if (helpFiles.size() > 0) {
                HelpFile helpFile = helpFiles.get(0);
                Helper.logMethodExitAndOutputParameters(logger, methodName, true, helpFile);
                return helpFile;
            }
        } catch (DataAccessException e) {
            Helper.logException(logger, methodName, e);
            throw new DataSetProcessingException("Data access error occurs", e);
        }

        Helper.logMethodExitAndOutputParameters(logger, methodName, true, null);
        return null;
    }

    /**
     * Gets the data set with the given ID.
     *
     * @param dataSetId the ID of the data set
     * @return the data set
     * @throws DataSetProcessingException if there is an error while getting the data
     */
    public DataSet getDataSet(long dataSetId) throws DataSetProcessingException {
        String name = CLASS_NAME + ".getDataSet(long dataSetId)";
        Helper.logMethodEntryAndInputParameters(logger, name, new String[] {"dataSetId"},
                new Object[] {dataSetId});
        try {
            DataSet dataSet = null;
            List<DataSet> dataSets = jdbcTemplate.query(
                    "select id, data_set_text_id, name, start_time, stop_time, description from dataset where id = ?",
                    new Object[] {dataSetId},
                    new RowMapper<DataSet>() {
                        public DataSet mapRow(ResultSet rs, int rowNum) throws SQLException {
                            DataSet dataSet = new DataSet();
                            dataSet.setId(rs.getLong("id"));
                            dataSet.setTextId(rs.getString("data_set_text_id"));
                            dataSet.setName(rs.getString("name"));
                            dataSet.setStartDate(rs.getTimestamp("start_time"));
                            dataSet.setStopDate(rs.getTimestamp("stop_time"));
                            dataSet.setDescription(rs.getString("description"));
                            return dataSet;
                        }
                    });
            if (dataSets.size() > 0) {
                dataSet = dataSets.get(0);
                getSupportingData(dataSet);
            }
            Helper.logMethodExitAndOutputParameters(logger, name, true, dataSet);
            return dataSet;
        } catch (DataAccessException e) {
            Helper.logException(logger, name, e);
            throw new DataSetProcessingException("Data access error occurs", e);
        }
    }

    /**
     * Gets the data sets that meet the given criteria.
     *
     * @param criteria the criteria to match
     * @param page the 1-based page
     * @param pageSize the size of the page
     * @return the data set that matches the criteria
     * @throws DataSetProcessingException if there is an error while getting the data
     */
    public PagedResults<DataSet> searchDataSets(DataSetSearchCriteria criteria, int page,
            int pageSize) throws DataSetProcessingException {
        String name = CLASS_NAME
            + "searchDataSets(DataSetSearchCriteria criteria, int page, int pageSize)";
        Helper.logMethodEntryAndInputParameters(logger, name, new String[] {"criteria", "page", "pageSize"},
                new Object[] {criteria, page, pageSize});
        try {
            StringBuilder whereSql = new StringBuilder();
            List<String> joinTableNames = new ArrayList<String>();
            Object[] values = new Object[1000];
            int i = 0;
            if (criteria.getId() != 0) {
                whereSql.append(" and ");
                whereSql.append("dataset.id = ?");
                values[i++] = criteria.getId();
            }

            i = processSql(criteria.getInstrumentHosts(), whereSql, joinTableNames, values, "instrument_host", i);
            i = processSql(criteria.getInstruments(), whereSql, joinTableNames, values, "instrument", i);
            if (criteria.getLowerDataRating() != null) {
                whereSql.append(" and ");
                whereSql.append("rating >= ?");
                values[i++] = criteria.getLowerDataRating();
                joinTableNames.add("rating");
            }
            if (criteria.isWhiteSpots()) {
                whereSql.append(" and ");
                whereSql.append("(rating > ? or rating > ?)");
                values[i++] = whiteSpotRatingThreshold;
                values[i++] = whiteSpotRatingPercentThreshold;
                if (!joinTableNames.contains("rating")) {
                    joinTableNames.add("rating");
                }
            }
            i = processSql(criteria.getMissions(), whereSql, joinTableNames, values, "mission", i);
            if (criteria.getStartDate() != null) {
                whereSql.append(" and ");
                whereSql.append("start_time >= ?");
                values[i++] = criteria.getStartDate();
            }
            if (criteria.getStopDate() != null) {
                whereSql.append(" and ");
                whereSql.append("stop_time <= ?");
                values[i++] = criteria.getStopDate();
            }
            i = processSql(criteria.getTargets(), whereSql, joinTableNames, values, "target", i);
            i = processSql(criteria.getTargetTypes(), whereSql, joinTableNames, values, "target_type", i);
            if (criteria.getUpperDataRating() != null) {
                whereSql.append(" and ");
                whereSql.append("rating <= ?");
                values[i++] = criteria.getUpperDataRating();
                if (!joinTableNames.contains("rating")) {
                    joinTableNames.add("rating");
                }
            }

            StringBuilder sql = new StringBuilder();
            StringBuilder innerJoinSql = new StringBuilder();
            if (whereSql.toString().trim().length() > 0) {
                if (joinTableNames.contains("instrument") && joinTableNames.contains("instrument_host")) {
                    joinTableNames.remove("instrument");
                }
                if (joinTableNames.contains("target") && joinTableNames.contains("target_type")) {
                    joinTableNames.remove("target");
                }
                for (String joinTableName : joinTableNames) {
                    if (joinTableName.equals("instrument_host")) {
                        innerJoinSql.append(
                                " inner join instrument_catalog on instrument_catalog.dataset_id = dataset.id")
                                .append(" inner join instrument on instrument_catalog.instrument_id = instrument.id")
                                .append(" inner join instrument_host_instrument on")
                                .append(" instrument_host_instrument.instrument_id = instrument.id")
                                .append(" inner join instrument_host on instrument_host_instrument.instrument_host_id")
                                .append(" = instrument_host.id");
                    }
                    if (joinTableName.equals("instrument")) {
                        innerJoinSql.append(
                                " inner join instrument_catalog on instrument_catalog.dataset_id = dataset.id")
                                .append(" inner join instrument on instrument_catalog.instrument_id = instrument.id");
                    }
                    if (joinTableName.equals("mission")) {
                        innerJoinSql.append(" inner join dataset_mission on dataset_mission.dataset_id = dataset.id")
                                .append(" inner join mission on dataset_mission.mission_id = mission.id");
                    }
                    if (joinTableName.equals("target")) {
                        innerJoinSql.append(" inner join dataset_target on dataset_target.dataset_id = dataset.id")
                                .append(" inner join target on dataset_target.target_id = target.id");
                    }
                    if (joinTableName.equals("target_type")) {
                        innerJoinSql.append(" inner join dataset_target on dataset_target.dataset_id = dataset.id")
                                .append(" inner join target on dataset_target.target_id = target.id")
                                .append(" inner join target_type_target on target_type_target.target_id = target.id")
                                .append(
                                    " inner join target_type on target_type_target.target_type_id = target_type.id");
                    }
                    if (joinTableName.equals("rating")) {
                        innerJoinSql.append(" inner join dataset_rating on dataset_rating.dataset_id = dataset.id");
                    }
                }
                sql.append(innerJoinSql);
                sql.append(" where ").append(whereSql.substring(4));
            }
            int start = pageSize * (page - 1);
            PagedResults<DataSet> result = new PagedResults<DataSet>();
            queryPagedDataSet(pageSize, start, sql.toString(), result, values);
            for (DataSet dataSet : result.getResults()) {
                getSupportingData(dataSet);
            }
            Helper.logMethodExitAndOutputParameters(logger, name, true, result);
            return result;
        } catch (DataAccessException e) {
            Helper.logException(logger, name, e);
            throw new DataSetProcessingException("Data access error occurs", e);
        }
    }

    /**
     * Gets the data sets that meet the given query.
     *
     * @param adHocQueryString the query to match
     * @param page the 1-based page
     * @param pageSize the size of the page
     * @return the data set that matches the criteria
     * @throws DataSetProcessingException if there is an error while getting the data
     */
    public PagedResults<DataSet> searchDataSets(String adHocQueryString, int page,
            int pageSize) throws DataSetProcessingException {
        String name = CLASS_NAME
            + "searchDataSets(String adHocQueryString, int page, int pageSize)";
        Helper.logMethodEntryAndInputParameters(logger, name,
                new String[] {"adHocQueryString", "page", "pageSize"},
                new Object[] {adHocQueryString, page, pageSize});
        try {
         // Get the translation of the adHoc query
            String structuredQuery = adHocQueryTranslator.translateQuery(adHocQueryString);
            // Use the resultant query directly with  jdbcTemplate.query to get the data_set entry with
            // the given criteria, then get the supporting data via additional queries.
            int start = pageSize * (page - 1);
            PagedResults<DataSet> result = new PagedResults<DataSet>();
            queryPagedDataSet(pageSize, start, adHocQueryString, result);
            for (DataSet dataSet : result.getResults()) {
                getSupportingData(dataSet);
            }

            // If not new, increment usage_count by 1
            if (jdbcTemplate.queryForInt("select count(id) from popular_query where query = ?",
                    new Object[] {structuredQuery}) > 0) {
                jdbcTemplate.update("update popular_query set usage_count = usage_count + 1 where query = ?",
                        new Object[] {structuredQuery});
            } else {
                // If new, add this query in popular_query with usage_count of 1
                jdbcTemplate.update("insert into popular_query (query, usage_frequency, usage_count)"
                        + " values (?, ?, ?)", new Object[] {structuredQuery, 0, 1});
            }
            Helper.logMethodExitAndOutputParameters(logger, name, true, result);
            return result;
        } catch (DataAccessException e) {
            Helper.logException(logger, name, e);
            throw new DataSetProcessingException("Data access error occurs", e);
        }
    }

    /**
     * Gets the related names of the given entity with the key word. It would search for names of the
     * objects.
     *
     * @param clazz the class of the object
     * @param keyword the keyword to look for
     * @return the list of all related terms
     * @throws DataSetProcessingException if there is an error while getting the data
     */
    public List<String> getRelatedNames(Class<?> clazz, String keyword) throws DataSetProcessingException {
        String name = CLASS_NAME + ".getRelatedNames(Class<?> clazz, String keyword)";
        Helper.logMethodEntryAndInputParameters(logger, name, new String[] {"clazz", "keyword"},
                new Object[] {clazz, keyword});
        List<String> result = null;
        try {
            String template = "select name from %s where name like ?";
            if (clazz == DataSet.class) {
                result = getNames(String.format(template, "dataset"), keyword);
            } else if (clazz == Mission.class) {
                result =  getNames(String.format(template, "mission"), keyword);
            } else if (clazz == Instrument.class) {
                result =  getNames(String.format(template, "instrument"), keyword);
            } else if (clazz == TargetType.class) {
                result =  getNames(String.format(template, "target_type"), keyword);
            } else if (clazz == Target.class) {
                result =  getNames(String.format(template, "target"), keyword);
            }
            Helper.logMethodExitAndOutputParameters(logger, name, true, result);
            return result;
        } catch (DataAccessException e) {
            Helper.logException(logger, name, e);
            throw new DataSetProcessingException("Data access error occurs", e);
        }
    }

    /**
     * Gets the data sets that meet the given query.
     *
     * @param predefinedQueryName the query to match
     * @param page the 1-based page
     * @param pageSize the size of the page
     * @return the data set that matches the criteria
     * @throws DataSetProcessingException if there is an error while getting the data
     */
    public PagedResults<DataSet> searchDataSetsByPredefinedQuery(String predefinedQueryName, int page,
            int pageSize) throws DataSetProcessingException {
        String name = CLASS_NAME + ".searchDataSetsByPredefinedQuery(String predefinedQueryName, int page, "
            + "int pageSize)";
        Helper.logMethodEntryAndInputParameters(logger, name,
                new String[] {"predefinedQueryName", "page", "pageSize"},
                new Object[] {predefinedQueryName, page, pageSize});
        try {
            // Use jdbcTemplate to run the query to get the rows of data_set entries. The use the template to
            // get related tale info
            int start = pageSize * (page - 1);
            List<String> queries = jdbcTemplate.queryForList("select query from predefined_query where name = ?",
                    new Object[] {predefinedQueryName}, String.class);
            PagedResults<DataSet> result = new PagedResults<DataSet>();
            result.setResults(new ArrayList<DataSet>());
            if (queries.size() > 0) {
                queryPagedDataSet(pageSize, start, queries.get(0), result);
            }
            for (DataSet dataSet : result.getResults()) {
                getSupportingData(dataSet);
            }
            Helper.logMethodExitAndOutputParameters(logger, name, true, result);
            return result;
        } catch (DataAccessException e) {
            Helper.logException(logger, name, e);
            throw new DataSetProcessingException("Data access error occurs", e);
        }
    }

    /**
     * Gets all predefined queries.
     *
     * @return the list of all predefined queries
     * @throws DataSetProcessingException if there is an error while getting the data
     */
    public List<String> getPredefinedQueries() throws DataSetProcessingException {
        String name = CLASS_NAME + ".getPredefinedQueries()";
        Helper.logMethodEntryAndInputParameters(logger, name, null, null);
        try {
            List<String> result = jdbcTemplate.queryForList("select query from predefined_query",
                    String.class);
            Helper.logMethodExitAndOutputParameters(logger, name, true, result);
            return result;
        } catch (DataAccessException e) {
            Helper.logException(logger, name, e);
            throw new DataSetProcessingException("Data access error occurs", e);
        }
    }

    /**
     * Gets all popular queries.
     *
     * @return the list of all predefined queries
     * @throws DataSetProcessingException if there is an error while getting the data
     */
    public List<String> getPopularQueries() throws DataSetProcessingException {
        String name = CLASS_NAME + ".getPopularQueries()";
        Helper.logMethodEntryAndInputParameters(logger, name, null, null);
        try {
            List<String> result = jdbcTemplate.queryForList(
                    "select query from popular_query order by usage_count desc LIMIT 0, "
                        + popularQueryRetrievalThreshold,
                    String.class);
            Helper.logMethodExitAndOutputParameters(logger, name, true, result);
            return result;
        } catch (DataAccessException e) {
            Helper.logException(logger, name, e);
            throw new DataSetProcessingException("Data access error occurs", e);
        }
    }

    /**
     * Gets the relates. Only set the id.
     * @param dataSet the data set to set data
     */
    private void getSupportingData(DataSet dataSet) {
        dataSet.setInstruments(jdbcTemplate.query(
                "select instrument_id from instrument_catalog where dataset_id = ? ",
                new Object[] {dataSet.getId()},
                new RowMapper<Instrument>() {
                    public Instrument mapRow(ResultSet rs, int rowNum) throws SQLException {
                        Instrument instrument = new Instrument();
                        instrument.setId(rs.getLong("instrument_id"));
                        return instrument;
                    }
                }));
        dataSet.setMissions(jdbcTemplate.query(
                "select mission_id from dataset_mission where dataset_id = ? ",
                new Object[] {dataSet.getId()},
                new RowMapper<Mission>() {
                    public Mission mapRow(ResultSet rs, int rowNum) throws SQLException {
                        Mission mission = new Mission();
                        mission.setId(rs.getLong("mission_id"));
                        return mission;
                    }
                }));
        dataSet.setReferences(jdbcTemplate.query(
                "select reference_id from reference_catalog where dataset_id = ? ",
                new Object[] {dataSet.getId()},
                new RowMapper<Reference>() {
                    public Reference mapRow(ResultSet rs, int rowNum) throws SQLException {
                        Reference reference = new Reference();
                        reference.setId(rs.getLong("reference_id"));
                        return reference;
                    }
                }));
        dataSet.setTargets(jdbcTemplate.query(
                "select target_id from dataset_target where dataset_id = ? ",
                new Object[] {dataSet.getId()},
                new RowMapper<Target>() {
                    public Target mapRow(ResultSet rs, int rowNum) throws SQLException {
                        Target target = new Target();
                        target.setId(rs.getLong("target_id"));
                        return target;
                    }
                }));
        dataSet.setVolumes(jdbcTemplate.query(
                "select volume_id from dataset_volume where dataset_id = ? ",
                new Object[] {dataSet.getId()},
                new RowMapper<Volume>() {
                    public Volume mapRow(ResultSet rs, int rowNum) throws SQLException {
                        Volume volume = new Volume();
                        volume.setId(rs.getLong("volume_id"));
                        return volume;
                    }
                }));

        List<Map<String, Object>> ratingList =
            jdbcTemplate.queryForList("select rating from dataset_rating where dataset_id = ?",
                new Object[] {dataSet.getId()});
        if (ratingList.size() > 0) {
            dataSet.setRating(((Double) ratingList.get(0).get("rating")).floatValue());
        }

        dataSet.setOtherChildren(jdbcTemplate.query(
                "select id from dataset_metadata where dataset_id = ? ",
                new Object[] {dataSet.getId()},
                new RowMapper<MetadataObject>() {
                    public MetadataObject mapRow(ResultSet rs, int rowNum) throws SQLException {
                        MetadataObject metadataObject = new MetadataObject();
                        metadataObject.setId(rs.getLong("id"));
                        return metadataObject;
                    }
                }));
    }

    /**
     * Process the where sql for different type of table.
     * @param strs the criteria value
     * @param whereSql the where sql
     * @param joinTableNames the list of table names type to add
     * @param values the search values
     * @param joinTableName the table names type needed to add into joinTableNames parameter if str
     *                      parameter is not empty
     * @param i the index of values parameter
     *
     * @return the index of values parameter
     */
    private int processSql(List<String> strs, StringBuilder whereSql, List<String> joinTableNames,
            Object[] values, String joinTableName, int i) {
        if (strs != null && strs.size() > 0) {
            int count = 0;
            for (String str : strs) {
                if (count == 0) {
                    whereSql.append(" and (");
                } else {
                    whereSql.append(" or ");
                }
                whereSql.append(joinTableName + ".name = ?");
                values[i++] = str;
                count++;
            }
            whereSql.append(")");
            joinTableNames.add(joinTableName);
        }
        return i;
    }

    /**
     * Gets the names for different tables base on the sql and keyword.
     *
     * @param sql the sql
     * @param keyword the keyword value
     * @return a list of names
     */
    private List<String> getNames(String sql, String keyword) {
        StringBuilder newKeyword = new StringBuilder();
        newKeyword.append("%").append(keyword).append("%");
        return jdbcTemplate.queryForList(sql, new Object[] {newKeyword},
                String.class);
    }

    /**
     * Gets the paged results for searches by given criteria.
     *
     * @param pageSize the page size
     * @param start the start id
     * @param query the query
     * @param result the paged results instance to set
     * @param values the param values
     */
    private void queryPagedDataSet(int pageSize, int start, String query,
            PagedResults<DataSet> result, Object[] values) {
        StringBuilder sql = new StringBuilder();
        StringBuilder countSql = new StringBuilder();

        sql.append("select dataset.id, dataset.data_set_text_id, dataset.name, dataset.start_time,"
                + " dataset.stop_time, dataset.description from dataset");
        countSql.append("select count(distinct dataset.id) from dataset");

        // Set where clause and inner join clause
        if (query.trim().length() > 0) {
            sql.append(query);
            countSql.append(query);
        }

        sql.append(" group by dataset.id");
        sql.append(" limit ").append(start).append(",").append(pageSize); // Set paging

        // Specifies the length of values, otherwise, query by jdbcTemplate will cause error.
        int i = 0;
        for (Object value : values) {
            if (value != null) {
                i++;
            }
        }
        Object[] newValues = new Object[i];
        for (int j = 0; j < i; j++) {
            newValues[j] = values[j];
        }

        result.setTotal(jdbcTemplate.queryForInt(countSql.toString(), newValues));
        result.setResults(jdbcTemplate.query(sql.toString(), newValues,
                new RowMapper<DataSet>() {
                    public DataSet mapRow(ResultSet rs, int rowNum) throws SQLException {
                        DataSet dataSet = new DataSet();
                        dataSet.setId(rs.getLong("id"));
                        dataSet.setTextId(rs.getString("data_set_text_id"));
                        dataSet.setName(rs.getString("name"));
                        dataSet.setStartDate(rs.getDate("start_time"));
                        dataSet.setStopDate(rs.getDate("stop_time"));
                        dataSet.setDescription(rs.getString("description"));
                        return dataSet;
                    }
                }));
    }

    /**
     * Gets the paged results for searches by given query.
     *
     * @param pageSize the page size
     * @param start the start id
     * @param query the query
     * @param result the paged results instance to set
     */
    private void queryPagedDataSet(int pageSize, int start, String query,
            PagedResults<DataSet> result) {
        StringBuilder sql = new StringBuilder();
        StringBuilder countSql = new StringBuilder();

        sql.append(query);
        countSql.append(query);

        // Replace the strings which are between 'select ' and ' from', with 'count(dataset.id)'
        // As the query is something like 'select * from dataset where ... inner join ... on ... ',
        // assume all fields are selected
        countSql.replace(query.toLowerCase().indexOf("select") + 7,
                query.toLowerCase().indexOf("from") - 1, "count(dataset.id)");

        sql.append(" limit ").append(start).append(",").append(pageSize);

        result.setTotal(jdbcTemplate.queryForInt(countSql.toString()));
        result.setResults(jdbcTemplate.query(sql.toString(),
                new RowMapper<DataSet>() {
                    public DataSet mapRow(ResultSet rs, int rowNum) throws SQLException {
                        DataSet dataSet = new DataSet();
                        dataSet.setId(rs.getLong("id"));
                        dataSet.setTextId(rs.getString("data_set_text_id"));
                        dataSet.setName(rs.getString("name"));
                        dataSet.setStartDate(rs.getDate("start_time"));
                        dataSet.setStopDate(rs.getDate("stop_time"));
                        dataSet.setDescription(rs.getString("description"));
                        return dataSet;
                    }
                }));
    }
}